package edu.gatech.cs2340.group7.wheresmystuffapp.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Contains methods for creating and updating the database
 * 
 * @author tbowling3
 * 
 */
public class MyDbHelper extends SQLiteOpenHelper {
	public static final String KEY_ROWID = "_id";
	private static final String WMS_DATABASE_NAME = "WheresMyStuffDb";
	private static final int DATABASE_VERSION = 2;

	public static final String USER_TABLE_NAME = "userTable";
	// public static final String KEY_ROW = "rowId";
	public static final String KEY_NAME = "username";
	public static final String KEY_USER_PASSWORD = "userPassword";
	public static final String KEY_ACCOUNT_CREATED_DATE = "accountCreatedDate";
	public static final String KEY_ACCOUNT_LOCKED = "accountLocked";
	public static final String KEY_ACCOUNT_PHONE = "accountPhoneNumber";
	public static final String KEY_IS_ADMIN = "accountIsAdmin";

	public static final String ITEM_TABLE_NAME = "itemTable";
	public static final String KEY_ITEM_NAME = "itemName";
	public static final String KEY_ITEM_USER_NAME = "username";
	public static final String KEY_CATEGORY = "itemCategory";
	public static final String KEY_ITEM_DESCRIPTION = "itemDescription";
	public static final String KEY_ITEM_RESOLVE = "itemIsResolved";
	public static final String KEY_LOCATION = "itemLocation";
	public static final String KEY_LATITUDE = "itemLocationLatitude";
	public static final String KEY_LONGITUDE = "itemLocationLongitude";
	public static final String KEY_PICTURE_ID = "itemPictureId";
	public static final String KEY_ITEM_DATE_ENTERED = "itemDateEntered";

	public static final String LOST_ITEM_ID = "lostItemId";
	public static final String LOST_ITEM_TABLE_NAME = "lostItemTable";
	public static final String KEY_LOST_ITEM_NAME = "lostItemName";
	public static final String KEY_DATE_LOST = "lostItemDateLost";
	public static final String KEY_LOST_ITEM_REWARD = "lostItemReward";

	public static final String FOUND_ITEM_ID = "foundItemId";
	public static final String FOUND_ITEM_TABLE_NAME = "foundItemTable";
	public static final String KEY_FOUND_ITEM_NAME = "foundItemName";
	public static final String KEY_DATE_FOUND = "foundItemDateLost";
	
	public static final String DONATED_ITEM_TABLE_NAME = "donatedItemTable";
	public static final String DONATED_ITEM_ID = "donatedItemId";
	
	public static final String DONATION_REQUEST_ITEM_TABLE_NAME = "donationRequestItemTable";
	public static final String DONATION_REQUEST_ITEM_ID = "donationRequestItemId";

	public MyDbHelper(Context context) {
		super(context, WMS_DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(MyDbHelper.class.getName(), "Ready to create databases");
		// Create User Table
		db.execSQL("CREATE TABLE " + USER_TABLE_NAME + "(" + 
				KEY_NAME + " text primary key, " + 
				KEY_USER_PASSWORD + " text not null, " + 
				KEY_ACCOUNT_CREATED_DATE + " text not null, " + 
				KEY_ACCOUNT_LOCKED + " integer not null, " + 
				KEY_ACCOUNT_PHONE + " text not null, " + 
				KEY_IS_ADMIN + " integer not null, "
				+ "FOREIGN KEY(" + KEY_NAME + ") REFERENCES " + USER_TABLE_NAME
				+ "(" + KEY_NAME + ")" + ");");

		Log.d(MyDbHelper.class.getName(),
				"User Created Database. Ready to create Item database");
		// Create Item Table
		db.execSQL("CREATE TABLE " + ITEM_TABLE_NAME + "(" +
				KEY_ROWID	+ " INTEGER PRIMARY KEY, " +
				KEY_ITEM_USER_NAME + " text not null, " +
				KEY_ITEM_NAME + " text, " +
				KEY_CATEGORY + " text not null, " +
				KEY_ITEM_DESCRIPTION + " text not null, " +
				KEY_ITEM_RESOLVE + " integer not null, " +
				KEY_LONGITUDE + " integer not null, " +
				KEY_LATITUDE  + " integer not null, " +
				KEY_PICTURE_ID + " BLOB, " + 
				KEY_ITEM_DATE_ENTERED + " text not null, " + "FOREIGN KEY(" + 
				KEY_ITEM_USER_NAME + ") REFERENCES " + 
				USER_TABLE_NAME + "(" + KEY_NAME + ")" + 
				");");

		Log.d(MyDbHelper.class.getName(),
				"Item Database Created. Ready to create Lost Item database");
		// Create Lost Item Table
		db.execSQL("CREATE TABLE " + LOST_ITEM_TABLE_NAME + "(" + LOST_ITEM_ID
				+ " integer primary key, " + KEY_LOST_ITEM_NAME + " text, "
				+ KEY_DATE_LOST + " text not null, " + KEY_LOST_ITEM_REWARD
				+ " text not null, " + "FOREIGN KEY(" + LOST_ITEM_ID
				+ ") REFERENCES " + ITEM_TABLE_NAME + "(" + KEY_ROWID + ")"
				+ ");");

		Log.d(MyDbHelper.class.getName(),
				"Lost Item Database Created. Ready to create Found Item database");
		// Create Found Item Table
		db.execSQL("CREATE TABLE " + FOUND_ITEM_TABLE_NAME + "("
				+ FOUND_ITEM_ID + " integer primary key, "
				+ KEY_FOUND_ITEM_NAME + " text, " + KEY_DATE_FOUND
				+ " text not null, " + "FOREIGN KEY(" + FOUND_ITEM_ID
				+ ") REFERENCES " + ITEM_TABLE_NAME + "(" + KEY_ROWID + ")"
				+ ");");

		Log.d(MyDbHelper.class.getName(), "Found Item Database Created");
		
		Log.d(MyDbHelper.class.getName(), "Ready to create donated items database");
		db.execSQL("CREATE TABLE " + DONATED_ITEM_TABLE_NAME + "("
				+ DONATED_ITEM_ID + " integer primary key"
				+ " REFERENCES " + ITEM_TABLE_NAME + "(" + KEY_ROWID + ") " 
				+ ");");
		
		Log.d(MyDbHelper.class.getName(), "Donated Item Database Created");
		
		Log.d(MyDbHelper.class.getName(), "Ready to create donation request items database");
		db.execSQL("CREATE TABLE " + DONATION_REQUEST_ITEM_TABLE_NAME + "("
				+ DONATION_REQUEST_ITEM_ID + " integer primary key"
				+ " REFERENCES " + ITEM_TABLE_NAME + "(" + KEY_ROWID + ") " 
				+ ");");
		
		Log.d(MyDbHelper.class.getName(), "Donation Request Item Database Created");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(MyDbHelper.class.getName(), "Upgrading database from version "
				+ oldVersion + " to " + newVersion
				+ ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS " + USER_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + ITEM_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + LOST_ITEM_TABLE_NAME);
		db.execSQL("DROP TABLE IF EXISTS " + FOUND_ITEM_TABLE_NAME);
		onCreate(db);

	}
}
